package dth.dao;

import java.sql.Date;

import javassist.expr.NewArray;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;

import dth.entity.Account;
import dth.entity.OrderSetupBox;

import dth.utils.DthSessionConstant;
import dth.utils.DthUtil;

public class OrderSetupBoxDAO {
	private SessionFactory sessionFactory=new AnnotationConfiguration().configure().buildSessionFactory();
	private Session session=sessionFactory.openSession();
	
	public java.util.List<OrderSetupBox> getListOrderSetupBox1() // List da~ thanh toan  cua user or dis our dealer
	{
		session.beginTransaction();
		Account ac = new Account();
		if(DthUtil.getAccountDTH()!=null)
			ac= DthUtil.getAccountDTH();
		String queryString = "select * from order_setup_box where fk_distributor_id=:iddis and fk_dealer_id=:iddealer and fk_user_id=:iduser and status=:status ";
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		query.setParameter("iddealer",ac.getFkDealerId());
		query.setParameter("iddis",ac.getFkDistributorId());
		query.setParameter("iduser",ac.getFkUserId());
		query.setParameter("status",1);
		return query.list();
	}
	public java.util.List<OrderSetupBox> getListOrderSetupBox2() // List chua thanh toan cua user or dis our dealer
	{
		session.beginTransaction();
		Account ac = new Account();
		if(DthUtil.getAccountDTH()!=null)
			ac= DthUtil.getAccountDTH();
		String queryString = "select * from order_setup_box where fk_distributor_id=:iddis and fk_dealer_id=:iddealer and fk_user_id=:iduser and status=:status ";
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		query.setParameter("iddealer",ac.getFkDealerId());
		query.setParameter("iddis",ac.getFkDistributorId());
		query.setParameter("iduser",ac.getFkUserId());
		query.setParameter("status",0);
		return query.list();
	}
	public java.util.List<OrderSetupBox> getListOrderSetupBox3() // all of user or dis or dealer
	{
		session.beginTransaction();
		Account ac = new Account();
		if(DthUtil.getAccountDTH()!=null)
			ac= DthUtil.getAccountDTH();
		String queryString = "select * from order_setup_box where fk_distributor_id=:iddis and fk_dealer_id=:iddealer and fk_user_id=:iduser ";
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		query.setParameter("iddealer",ac.getFkDealerId());
		query.setParameter("iddis",ac.getFkDistributorId());
		query.setParameter("iduser",ac.getFkUserId());
		
		return query.list();
	}
	
	public java.util.List<OrderSetupBox> getListOrderSetupBox4() // all in db
	{
		
		String queryString = "select * from order_setup_box";
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		return query.list();
	}
	
	public java.util.List<OrderSetupBox> getListOrderSetupBox5() // tất cả các order chưa thanh toán
	{
		session.beginTransaction();
		Account ac = new Account();
		if(DthUtil.getAccountDTH()!=null)
			ac= DthUtil.getAccountDTH();
		String queryString = "select * from order_setup_box where status=0 ";
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		return query.list();
	}
	
	public java.util.List<OrderSetupBox> getListOrderSetupBox6() // tất cả các order đã thanh toán
	{
		session.beginTransaction();
		Account ac = new Account();
		if(DthUtil.getAccountDTH()!=null)
			ac= DthUtil.getAccountDTH();
		String queryString = "select * from order_setup_box where status=1 ";
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		return query.list();
	}
	
	public java.util.List<OrderSetupBox> getListOrderSetupBox(boolean a, boolean b)
	{
		session.beginTransaction();
		Account ac = new Account();
		if(DthUtil.getAccountDTH()!=null)
			ac= DthUtil.getAccountDTH();
		String queryString = "";
		int flag =0;
		System.out.println("tesssssssssssssssssssttttttttttt "+ a +" &" +b);
		if(a == true && b == false)  // da thanh toan 
		{
			flag = 1;
			queryString="select * from order_setup_box where fk_distributor_id=:iddealer and fk_dealer_id=:iddis and fk_user_id=:iduser and status=:status ";
		}
		if((a== false && b == true) ||(a == false && b ==false)) // chua thanh toan
		{
			flag = 2;
			queryString="select * from order_setup_box where fk_distributor_id=:iddealer and fk_dealer_id=:iddis and fk_user_id=:iduser and status=:status ";
		}
		if(a == true && b == true)// chon het
		{
			queryString="select * from order_setup_box where fk_distributor_id=:iddealer and fk_dealer_id=:iddis and fk_user_id=:iduser ";
		}
		
		Query query=session.createSQLQuery(queryString).addEntity(OrderSetupBox.class);
		query.setParameter("iddealer",ac.getFkDealerId());
		query.setParameter("iddis",ac.getFkDistributorId());
		if(flag == 1)
		{
			query.setParameter("status",1);
		}
		if(flag == 2)
		{
			query.setParameter("status",0);
		}
		query.setParameter("iduser",ac.getFkUserId());
		
		
		return query.list();
	}
	public int delete(int id)
	{
		Transaction tx=session.beginTransaction();
		String queryString="delete from order_setup_box where id= :id";
		Query query=session.createSQLQuery(queryString).setParameter("id", id);
		int count=query.executeUpdate();
		tx.commit();
		return count;
	}
	public int insert(OrderSetupBox order)
	{
		
		if(checkInsert(order.getFkDealerId(),order.getFkDistributorId(),order.getFkSetupBox(),order.getFkUserId()))
		{
			return 2;
		}
		Transaction tx=session.beginTransaction();
		String queryString="";		
		queryString="INSERT into order_setup_box(fk_distributor_id,fk_dealer_id,date_insert,fk_user_id,fk_setup_box,quantity,status,price,setupboxtype) values(:dis_id,:dealer_id,:date,:user_id,:fk_setupbox,:quantity,:status,:price,:setupboxtype)";
		
		
		Query query=session.createSQLQuery(queryString);
		query.setParameter("dis_id",order.getFkDistributorId());
		query.setParameter("dealer_id",order.getFkDealerId());
		query.setParameter("date",order.getDateInsert());
		query.setParameter("user_id",order.getFkUserId());
		query.setParameter("fk_setupbox",order.getFkSetupBox());
		query.setParameter("quantity",order.getQuantity());
		query.setParameter("status",order.getStatus());
		query.setParameter("price",order.getPriceSTB());
		query.setParameter("setupboxtype",order.getTypeSTB());
		
		int count=query.executeUpdate();
		tx.commit();
		return count;
	}
	
	
	public int updateQuantity(int id, int qua)
	{
		Transaction tx=session.beginTransaction();
		String queryString="update order_setup_box set quantity=:quantity where id=:id";
		Query query=session.createSQLQuery(queryString);
		query.setParameter("id",id);
		query.setParameter("quantity",qua);
		
		int count=query.executeUpdate();
		tx.commit();
		return count;
	}
	
	public int updateStatus(int iddealer,int iddis,int iduser)
	{
		Transaction tx=session.beginTransaction();
		String queryString="update order_setup_box set status=1,date_payment=:date_payment  where status=0  and fk_distributor_id=:iddis and fk_dealer_id=:iddealer and fk_user_id=:iduser";
		Query query=session.createSQLQuery(queryString);
		java.util.Date date=new java.util.Date();//lay ngay hien tai
		query.setParameter("date_payment",DthUtil.convertToSqlDate(date));
		query.setParameter("iddealer",iddealer);
		query.setParameter("iddis",iddis);
		query.setParameter("iduser",iduser);
		System.out.println("  iddealer:"+ iddealer+" iddis= "+ iddis+"" );
		int count=query.executeUpdate();
		tx.commit();
		return count;
	}
	
	private Boolean checkInsert(int iddealer,int iddis, int idsetupbox,int iduser)
	{
		String queryString="select count(*) from order_setup_box where fk_distributor_id=:iddis and fk_dealer_id=:iddealer and fk_setup_box=:idsetupbox and status=:status and fk_user_id=:iduser";
		Query query=session.createSQLQuery(queryString);
		query.setParameter("iddealer",iddealer);
		query.setParameter("iddis",iddis);
		query.setParameter("idsetupbox",idsetupbox);
		query.setParameter("status",0);
		query.setParameter("iduser",iduser);
		System.out.println("  iddealer:"+ iddealer+" iddis= "+ iddis+" id setupbox= "+ idsetupbox +" count =" +query.list().get(0).toString());
		if(query.list().get(0).toString() == "0")
			return false;
		return true;
	}
}
